package Test_2019_2_25;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

class JDBC {
    //加载驱动
    static {
        try {
            // 用来检查给定的类名存不存在
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("驱动不存在，请添加驱动包！");
        }
    }

    /**
     * 关系：PreparedStatement继承自Statement,都是接口
     * 区别：PreparedStatement可以使用占位符，是预编译的，批处理比Statement效率高
     */
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private Statement stmt = null;

    // 开销时间
    public static void main(String[] args) throws Exception {
        for (int i = 0; i < 5; i++) {
            long start = System.currentTimeMillis();
            Connection conn = openConnection();
            long end = System.currentTimeMillis();
            System.out.println("时间开销：" + (end - start));
        }
    }

    /**
     * 获得数据库连接对象Connection
     */
    private static Connection openConnection() {
        //位置
        Connection conn = null;
        String DB_URL = "jdbc:mysql://" +
                "localhost:3306" +
                "/book" +
                "?useSSL=false&serverTimezone=UTC&characterEncoding=UTF8";
        //账号
        String USER = "root";
        //密码
        String PASS = "19980317";
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获得PreparedStatement
     */
    private PreparedStatement getPrepareStatement(String sql) {
        conn = openConnection();
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }

    /**
     * 释放资源
     */
    private void close() {
        try {
            if (pstmt != null && !pstmt.isClosed()) {
                pstmt.close();
            }
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将数据库中用户信息转为集合
     */
    List<User> Select() {
        List<User> list = new ArrayList<User>();
        String sql =
                "select user.id,user.name,user.age,user.sex,user.cid,dept.cname \n" +
                        "from user,dept \n" +
                        "where user.cid = dept.cid";
        PreparedStatement pstat = getPrepareStatement(sql);
        try {
            ResultSet rs = pstat.executeQuery();
            User bd;
            while (rs.next()) {
                bd = new User();
                bd.setId(rs.getInt("id"));
                bd.setName(rs.getString("name"));
                bd.setAge(rs.getInt("age"));
                bd.setSex(rs.getString("sex"));
                bd.setCid(rs.getInt("cid"));
                bd.setCname(rs.getString("dept.cname"));
                list.add(bd);
            }
            close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
